import tkinter as tk
from tkinter import scrolledtext
import pymssql


def open(s):
    window = tk.Tk()
    window.title("系统管理人员(修改)-" + s)
    window.geometry("500x300")
    # 设置列的权重
    window.grid_columnconfigure(0, weight=1)
    window.grid_columnconfigure(1, weight=2)
    window.grid_columnconfigure(2, weight=1)

    label1 = tk.Label(window, text="植物名称：")
    entry1 = tk.Entry(window)
    label2 = tk.Label(window, text="需要修改的信息：")
    entry2 = tk.Entry(window)
    conn = pymssql.connect(host='localhost', server='LAPTOP-QBHKFR9T', port='1433', user='Mysql',
                           password='15681743536',
                           database='植物管理系统', charset='utf8')

    if conn:
        print("连接成功！")

    def check_credentials1():
        message = entry1.get()
        new_message = entry2.get()
        cursor = conn.cursor()  # 获取游标
        sql = f"SELECT DISTINCT path FROM 分类 WHERE 分类.名称 = '{new_message}'"
        cursor.execute(sql)
        result1 = ""
        for row in cursor:  # 对每一行数据遍历输出
            numbers = row[0].split('/')
            result1 += "%s" % ('/'.join(numbers))

        cursor = conn.cursor()  # 获取游标
        sql = f"SELECT DISTINCT path FROM 分类 WHERE 分类.名称 = '{message}'"
        cursor.execute(sql)
        result2 = ""
        if cursor.rowcount == 0:  # 如果没有找到记录
            result = "无该植物信息"
            scrollbar.insert(tk.INSERT, result)
        else:
            for row in cursor:  # 对每一行数据遍历输出
                numbers = row[0].split('/')
                result2 = '/'.join(numbers[2:])

                print(result2)

            result3 = result1 + "/" + result2
            result2 = "修改后路径：" + result3
            scrollbar.insert(tk.INSERT, result2)
            print(result3)
            cursor = conn.cursor()  # 获取游标
            sql = f"UPDATE 分类 SET path='{result3}' FROM 分类 WHERE 分类.名称 = '{message}'"
            cursor.execute(sql)

            conn.commit()

    def check_credentials2():
        message = entry1.get()
        new_message = entry2.get()
        cursor = conn.cursor()  # 获取游标
        sql = f"SELECT DISTINCT path FROM 分类 WHERE 分类.名称 = '{new_message}'"
        cursor.execute(sql)
        result1 = ""
        for row in cursor:  # 对每一行数据遍历输出
            numbers = row[0].split('/')
            result1 += "%s" % ('/'.join(numbers))

        cursor = conn.cursor()  # 获取游标
        sql = f"SELECT DISTINCT path FROM 分类 WHERE 分类.名称 = '{message}'"
        cursor.execute(sql)
        result2 = ""
        if cursor.rowcount == 0:  # 如果没有找到记录
            result = "无该植物信息"
            scrollbar.insert(tk.INSERT, result)
        else:
            for row in cursor:  # 对每一行数据遍历输出
                numbers = row[0].split('/')
                result2 = '/'.join(numbers[3:])

                print(result2)

            result3 = result1 + "/" + result2
            result2 = "修改后路径：" + result3
            scrollbar.insert(tk.INSERT, result2)
            print(result3)
            cursor = conn.cursor()  # 获取游标
            sql = f"UPDATE 分类 SET path='{result3}' FROM 分类 WHERE 分类.名称 = '{message}'"
            cursor.execute(sql)

            conn.commit()

    def check_credentials3():
        message = entry1.get()
        new_message = entry2.get()
        cursor = conn.cursor()  # 获取游标
        sql = f"SELECT DISTINCT path FROM 分类WHERE 分类.名称 = '{new_message}'"
        cursor.execute(sql)
        result1 = ""
        for row in cursor:  # 对每一行数据遍历输出
            numbers = row[0].split('/')
            result1 += "%s" % ('/'.join(numbers))

        cursor = conn.cursor()  # 获取游标
        sql = f"SELECT DISTINCT path FROM 分类 WHERE 分类.名称 = '{message}'"
        cursor.execute(sql)
        result2 = ""
        if cursor.rowcount == 0:  # 如果没有找到记录
            result = "无该植物信息"
            scrollbar.insert(tk.INSERT, result)
        else:
            for row in cursor:  # 对每一行数据遍历输出
                numbers = row[0].split('/')
                result2 = '/'.join(numbers[4:])

                print(result2)

            result3 = result1 + "/" + result2
            result2 = "修改后路径：" + result3
            scrollbar.insert(tk.INSERT, result2)
            print(result3)
            cursor = conn.cursor()  # 获取游标
            sql = f"UPDATE 分类 SET path='{result3}' FROM 分类 WHERE 分类.名称 = '{message}'"
            cursor.execute(sql)

            conn.commit()

    def check_credentials4():
        message = entry1.get()
        new_message = entry2.get()

        cursor = conn.cursor()  # 获取游标
        sql = f"UPDATE 基本信息 SET 别名= '{new_message}' FROM 基本信息 WHERE 植物名称= '{message}'"
        cursor.execute(sql)
        if cursor.rowcount == 0:  # 如果没有找到记录
            result = "无该植物信息"
            scrollbar.insert(tk.INSERT, result)
        else:
            scrollbar.insert(tk.INSERT, "别名修改为：" + new_message)

            conn.commit()

    def check_credentials5():
        message = entry1.get()
        new_message = entry2.get()

        cursor = conn.cursor()  # 获取游标
        sql = f"UPDATE 生长环境 SET 生长环境= '{new_message}' FROM 生长环境,基本信息 WHERE 生长环境.植物编号=基本信息.植物编号 AND 植物名称= '{message}'"
        cursor.execute(sql)
        if cursor.rowcount == 0:  # 如果没有找到记录
            result = "无该植物信息"
            scrollbar.insert(tk.INSERT, result)
        else:
            scrollbar.insert(tk.INSERT, "生长环境修改为：" + new_message)

            conn.commit()

    def check_credentials6():
        message = entry1.get()
        new_message = entry2.get()
        cursor = conn.cursor()  # 获取游标
        sql = f"SELECT DISTINCT 县编号 FROM 分布区域 WHERE 分布区域.县 = '{new_message}'"
        cursor.execute(sql)
        result1 = ""
        for row in cursor:  # 对每一行数据遍历输出
            numbers = row[0].split('/')
            result1 += "%s" % ('/'.join(numbers))

        cursor = conn.cursor()  # 获取游标
        sql = f"UPDATE 分布 SET 县编号= '{result1}' FROM 分布,基本信息 WHERE 分布.植物编号= 基本信息.植物编号 AND 基本信息.植物名称= '{message}'"
        cursor.execute(sql)
        if cursor.rowcount == 0:  # 如果没有找到记录
            result = "无该植物信息"
            scrollbar.insert(tk.INSERT, result)
        else:
            scrollbar.insert(tk.INSERT, "植物分布县修改为：" + new_message)

            conn.commit()

    button1 = tk.Button(window, text="修改植物科", command=lambda: check_credentials1(), width=20, height=1)

    button2 = tk.Button(window, text="修改植物属", command=lambda: check_credentials2(), width=20,
                        height=1)  # 得到科，属，种，生长环境，分布区域
    button3 = tk.Button(window, text="修改植物种", command=lambda: check_credentials3(), width=20,
                        height=1)  # 仅查询得到植物情况即可
    button6 = tk.Button(window, text="修改植物别名", command=lambda: check_credentials4(), width=20,
                        height=1)  # 查询出对应植物
    button7 = tk.Button(window, text="修改植物生长环境", command=lambda: check_credentials5(), width=20,
                        height=1)  # 查询出对应植物
    button7 = tk.Button(window, text="修改植物分布区域", command=lambda: check_credentials6(), width=20,
                        height=1)  # 查询出对应植物

    back_button = tk.Button(window, text="返回上一级", command=window.destroy, width=20, height=1)

    label1.grid(row=0, column=0)
    entry1.grid(row=0, column=1)
    label2.grid(row=1, column=0)
    entry2.grid(row=1, column=1)
    button1.grid(row=2, column=0)
    button2.grid(row=2, column=1)
    button3.grid(row=2, column=2)
    button6.grid(row=3, column=0)
    button7.grid(row=3, column=1)

    back_button.grid(row=3, column=2)
    scrollbar = scrolledtext.ScrolledText(window, wrap=tk.WORD, width=60, height=10)
    scrollbar.grid(row=5, column=0, columnspan=4, sticky="nsew")
    window.mainloop()
    pass
